<?php
class Cm_model extends CI_Model {
public static $RET_MENU,$CHILD,$PARENT;
    function   __construct() {
        parent::__construct();
        
    }
	
	function cek_level($menu_id) {
		$this->db->select('count(strkode_upline) as record_count');
		$this->db->where('strkode_upline',$menu_id);
		$this->db->from('member');
		return $this->db->get()->row()->record_count;
	}
	 function child($strkode_upline, $lvl = 0, $slvl = 0) {
		//print_r($level_parent);
		$br = '';
		$lvl++;
		$sql_p = "select * from member where strkode_upline = '$strkode_upline'";
		//var_dump("select * from member where strkode_upline = 'M01027666'");
		$get_p = $this->db->query($sql_p);
		foreach ($get_p->result() as $r_p):
			$set_child = '';
			$folder = '';
			if ($this->cek_level($r_p->strkode_upline) > 0 ):
				$set_child = ', children: ['.$br;
				$slvl = $r_p->intid_dealer;
				$folder = ', isFolder: true';
			else:
				$slvl = 0;
			endif;
			//echo $slvl;
			$selected = '';
			//print_r($r_p);
			//$sql_u = "select * from prc_sys_user_menu where usr_id = '".$usrid."' and menu_id = '".$r_p->menu_id."'";
			//$get_u = $this->db->query($sql_u);
			//if($get_u->num_rows() > 0):
//				$selected = 'select: true, ';
			//endif;
			$selected = 'select: true, ';
			self::$CHILD .= '{'.$selected.'title: "'.$r_p->strnama_dealer.'", key: "'.$r_p->intid_dealer	.'"'.$folder.$set_child;
			$this->child($r_p->strkode_dealer, $lvl, $slvl);
			if ($r_p->intid_dealer == $slvl):
				self::$CHILD .= ']},'.$br;
			else:
				self::$CHILD .= '},'.$br;
			endif;
		endforeach;
	}

	function get_child() {
		return self::$CHILD;
	}
   
   function getMember($kode) {
        $this->db->select('member.*');
        $this->db->from('member');
        $this->db->where('member.strkode_dealer', $kode);
        return $this->db->get()->result();
    }

    function cek_bulan($bulan, $tahun) {
        $this->db->select('week.intid_week');
        $this->db->from('week');
        $this->db->where('week.intbulan', $bulan);
        $this->db->order_by('week.intid_week');
        return $this->db->get()->result();
    }

    function add($data) {
		$query=$this->db->query('select * from calon_manager where strkode_dealer = "'.$data['strkode_dealer'].'"');
		if($query->num_rows() > 0){
			return 0;
		}else{
			$this->db->insert('calon_manager', $data);
			return 1;
		}
    }

    function selectMother($keyword) {
        $query = $this->db->query("select b.strnama_dealer from unit a, member b where a.intid_unit = b.intid_unit and b.intlevel_dealer = 1");
        return $query->result();
    }

    function update_calon($kode, $data) {
        $this->db->where('strkode_dealer', $kode);
        $this->db->update('calon_manager', $data);
    }
	/**
	* sudah tidak berlaku lagi
	function update_member($kode, $data) {
        $this->db->where('strkode_dealer', $kode);
        $this->db->update('member', $data);
    }
	*/
    function getDownline($kode) {
        $this->db->select('member.*,unit.strnama_unit');
        $this->db->from('member');
        $this->db->where('member.strkode_upline', $kode);
        $this->db->join('unit', 'unit.intid_unit = member.intid_unit');
        $this->db->order_by('member.strnama_dealer', 'asc');
        return $this->db->get()->result();
    }

    function getWeek1($strkode) {
        $this->db->select('nota.datetgl');
        $this->db->from('nota');
        $this->db->where('nota.intid_dealer', $strkode);
        return $this->db->get()->result();
    }

    function getMonth($strkode) {
        $this->db->select('bulan as bul');
        $this->db->from('calon_manager');
        $this->db->where('calon_manager.strkode_dealer', $strkode);
        return $this->db->get()->result();
    }

    function weeks1($strkode, $week) {
        //print_r($week);
		$this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->where('nota.intid_week', $week);
        $this->db->group_by('nota.intno_nota');
        return $this->db->get()->result();
    }

    function luar_rekrut($strkode, $week, $bulan, $tahun) {
        $anak[] = $week;
        
        $pro='';

        for ($c=0;$c<count($week);$c++) {
            $pro.="'";
            $pro.=$week[$c]->intid_week;
            $pro.="'";
            $pro.=", ";
        }
		
        $pro = substr($pro, 0, -2);
        $this->db->select('member.strkode_dealer, nota.inttotal_omset as total, nota.intpv as pv, member.intlevel_dealer');
        $this->db->from('nota');
        $this->db->join('member', 'member.intid_dealer = nota.intid_dealer');
        $this->db->join('calon_manager', 'calon_manager.strkode_dealer = member.strkode_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where('nota.intid_jpenjualan', 10);
        $this->db->where("member.intid_week not in ($pro)");
		$this->db->where("calon_manager.is_cm <> 1");
        $this->db->where('MONTH(nota.datetgl)', $bulan);
        $this->db->where('YEAR(nota.datetgl)', $tahun);
        return $this->db->get()->result();
		
    }
	
	function cucu_luar_rekrut($strkode_dealer, $week, $bulan, $tahun)
	{
		$anak[] = $week;
        
        $pro='';

        for ($c=0;$c<count($week);$c++) {
            $pro.="'";
            $pro.=$week[$c]->intid_week;
            $pro.="'";
            $pro.=", ";
        }
		$this->db->select('nota.inttotal_omset as total, nota.intpv as pv');
        $this->db->from('nota');
        $this->db->join('member', 'member.intid_dealer = nota.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode_dealer);
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->where("member.intid_week not in ($pro)");
		$this->db->where("member.is_cm <> 1");
        $this->db->where('MONTH(nota.datetgl)', $bulan);
        $this->db->where('YEAR(nota.datetgl)', $tahun);
        return $this->db->get()->result();
	}

    function penjualanrekrut($strkode, $week) {
        /*
		$this->db->query('select if(
			(select count(*) 
				from 
					calon_manager inner join member on member.intid_dealer = calon_manager.intid_dealer 
				where calon_manager.is_cm = "1" or calon_manager.is_pra = "1" and member.strkode_upline = "'.$strkode_dealer.'") > 0,
			(select 
				sum(nota.inttotal_omset) as total, 
				nota.intid_week
			from nota inner join member on member.intid_dealer = nota.intid_dealer
			where 
				member.strkode_upline = "'.$strkode.'" and
				member.intid_week = "'.$week.'"
				nota.intid_jpenjualan = 1 group by nota.intid_week),
			(select 
				sum(nota.inttotal_omset) as total, 
				nota.intid_week
			from nota inner join member on member.intid_dealer = nota.intid_dealer
			where 
				member.strkode_upline = "'.$strkode.'" and
				member.intid_week = "'.$week.'" and 
				member.strkode_member not in (select member.strkode_member from 
					calon_manager inner join member on member.intid_dealer = calon_manager.intid_dealer 
				where calon_manager.is_cm = "1" or calon_manager.is_pra = "1" and member.strkode_upline = "'.$strkode_dealer.'"
				)
				nota.intid_jpenjualan = 1 group by nota.intid_week)
			)');
		*/
		/*
		$this->db->select('sum(nota.inttotal_omset) as total, nota.intid_week');
        $this->db->from('nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where('member.intid_week', $week);
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->group_by('nota.intid_week');
		
		//$this->db->query$this->db->query('select 
		//	(select count(*) from calon_manager where is_cm = "1" or is_pra = "1" and strkode_dealer = "member.strkode_dealer")as  from member where member.strkode_dealer = "'.$strkode_dealer.'");
		/*
		$query1 = $this->db->query('select strkode_dealer 
			from member m inner join calon_manager cm where m.strkode_dealer = cm.strkode_dealer 
			where
			m.strkode_upline = "'.$strkode.'" and 
			is_cm = "1" or is_pra = "1"');
			/*
			$row = $query1->result();
			$pro='';

			for ($c=0;$c<count($row);$c++) {
				$pro.="'";
				$pro.=$row->strkode_dealer;
				$pro.="'";
				$pro.=", ";
			}
			*
			$pro='';
			foreach($query1->result() as $row){
				$pro.="'";
				$pro.=$row->strkode_dealer;
				$pro.="'";
				$pro.=", ";
			}
			$pro = substr($pro, 0, -2);
			
			$this->db->select('sum(nota.inttotal_omset) as total, nota.intid_week');
			$this->db->from('nota');
			$this->db->join('member','member.intid_dealer = nota.intid_dealer');
        
			if(!empty($pro)){
				$this->db->where_not_in('member.strkode_dealer', $pro);	
			}
			$this->db->where('member.strkode_upline', $strkode);
			$this->db->where('member.intid_week', $week);
			$this->db->where('nota.intid_jpenjualan', 1);
			$this->db->group_by('nota.intid_week');
			
		/*
		$query = "select sum(intotal_omset) as total, nota.intid_week 
				from nota inner join member on member.intid_dealer = nota.intid_dealer 
				where member.strkode_upline = ''";
		///yang lama
		$this->db->select('sum(nota.inttotal_omset) as total, nota.intid_week');
			$this->db->from('nota');
			$this->db->join('member','member.intid_dealer = nota.intid_dealer');
        
		$this->db->where('member.strkode_upline', $strkode);
			$this->db->where('member.intid_week', $week);
			$this->db->where('nota.intid_jpenjualan', 1);
			$this->db->group_by('nota.intid_week');
		///
		select 
			sum(nota.inttotal_omset) as total, 
			nota.intid_week
		from nota inner join member on member.intid_dealer = nota.intid_dealer
		where 
			member.strkode_upline = "'.$strkode.'" and
			member.intid_week = "'.$week.'" and
			nota.intid_jpenjualan = 1 group by nota.intid_week');
		$this->db->query('select (select count(*) from calon_manager where is_cm = "1" or is_pra = "1" and strkode_dealer = "member.strkode_dealer") Z from member);
		*/
        //$query = "select sum(nota.inttotal_omset) as total, nota.intid_week from nota inner join member on member.intid_dealer = nota.intid_dealer where member.strkode_upline = '".$strkode."' and member.intid_week = '".$week."' and nota.intid_jpenjualan = '1' group by nota.intid_week";
		//$this->db->query($query);
		$query = $this->db->query('select sum(nota.inttotal_omset) as total, nota.intid_week 
		from nota inner join member on member.intid_dealer = nota.intid_dealer
		where member.strkode_upline = "'.$strkode.'" and member.intid_week = "'.$week.'" and nota.intid_jpenjualan = 1 group by "nota.intid_week"');
		/*
		$this->db->select('sum(nota.inttotal_omset) as total, nota.intid_week');
        $this->db->from('nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where('member.intid_week', $week);
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->group_by('nota.intid_week');
		*/
		return $query->result();
    }
    function week1($strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-01' and '$tahun-$bulan-07'");
        //$this->db->group_by('nota.intno_nota');
        return $this->db->get()->result();
    }
    function week2($strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-08' and '$tahun-$bulan-15'");
        //$this->db->group_by('nota.intno_nota');
        return $this->db->get()->result();
    }
    function week3($strkode, $bulan, $tahun) {
        $this->db->select('nota.inttotal_omset as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-16' and '$tahun-$bulan-23'");
        //$this->db->group_by('nota.intno_nota');
        return $this->db->get()->result();
    }
    function week4($strkode, $bulan, $tahun) {
        $this->db->select('nota.inttotal_omset as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-24' and '$tahun-$bulan-31'");
        //$this->db->group_by('nota.intno_nota');
        return $this->db->get()->result();
    }

    function rekrut1($strkode, $bulan, $tahun) {
        $this->db->select('count(member.strkode_dealer)as total');
        $this->db->from('member');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("member.datetanggal BETWEEN '$tahun-$bulan-01' and '$tahun-$bulan-07'");
        $this->db->group_by('member.datetanggal');
        return $this->db->get()->result();
    }

    function week_rekrut($strkode, $week) {
        $this->db->select('count(member.strkode_dealer)as total');
        $this->db->from('member');
        //$this->db->join('nota', 'nota.intid_dealer = member.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where('member.intid_week', $week);
        //$this->db->where('nota.intid_jpenjualan', 1);
        //$this->db->group_by('member.datetanggal');
        return $this->db->get()->result();
    }

    function rekrut2($strkode, $bulan, $tahun) {
        $this->db->select('count(member.strkode_dealer)as total');
        $this->db->from('member');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("member.datetanggal BETWEEN '$tahun-$bulan-08' and '$tahun-$bulan-15'");
        $this->db->group_by('member.datetanggal');
        return $this->db->get()->result();
    }

    function rekrut3($strkode, $bulan, $tahun) {
        $this->db->select('count(member.strkode_dealer)as total');
        $this->db->from('member');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("member.datetanggal BETWEEN '$tahun-$bulan-16' and '$tahun-$bulan-23'");
        $this->db->group_by('member.datetanggal');
        return $this->db->get()->result();
    }

    function rekrut4($strkode, $bulan, $tahun) {
        $this->db->select('count(member.strkode_dealer)as total');
        $this->db->from('member');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("member.datetanggal BETWEEN '$tahun-$bulan-24' and '$tahun-$bulan-31'");
        $this->db->group_by('member.datetanggal');
        return $this->db->get()->result();
    }

    function penjualanrekrut1($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        //$this->db->where("nota.intid_dealer <> $iddealer");
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-01' and '$tahun-$bulan-07'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function penjualanrekrut2($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        //$this->db->where("nota.intid_dealer <> $iddealer");
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-08' and '$tahun-$bulan-15'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function penjualanrekrut3($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        //$this->db->where("nota.intid_dealer <> $iddealer");
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-16' and '$tahun-$bulan-23'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function penjualanrekrut4($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        //$this->db->where("nota.intid_dealer <> $iddealer");
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-24' and '$tahun-$bulan-31'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function getidDealer($strkode) {
        $this->db->select('member.intid_dealer, member.intlevel_dealer');
        $this->db->from('member');
        $this->db->where('member.strkode_dealer', $strkode);
        return $this->db->get()->result();
    }



    function selectBarang($keyword) {
        $query = $this->db->query("select intid_barang, upper(strnama) strnama, intharga_jawa, intharga_luarjawa from barang where strnama like '$keyword%'");
        return $query->result();
    }

    function viewpenjualanrekrut1($strkode, $week) {
        $this->db->select('sum(nota.inttotal_omset) as total, nota.datetgl,nota.intno_nota, member.strnama_dealer');
        $this->db->from('nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->where('nota.intid_week', $week);
		$this->db->where('member.intid_week', $week);
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
		
        
    }

    function viewpenjualanrekrut2($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total, nota.datetgl, member.strnama_dealer');
        $this->db->from('nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-08' and '$tahun-$bulan-15'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function viewpenjualanrekrut3($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total, nota.datetgl, member.strnama_dealer');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where("nota.intid_dealer <> $iddealer");
        //$this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-16' and '$tahun-$bulan-23'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }
    function viewpenjualanrekrut4($iddealer, $strkode, $bulan, $tahun) {
        $this->db->select('sum(nota.inttotal_omset) as total, nota.datetgl, member.strnama_dealer');
        $this->db->from('nota');
        //$this->db->join('nota_detail', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->where("nota.intid_dealer <> $iddealer");
        //$this->db->where('member.strkode_upline', $strkode);
        $this->db->where("nota.datetgl BETWEEN '$tahun-$bulan-24' and '$tahun-$bulan-31'");
        $this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function update($strkode_dealer, $data) {
        $this->db->where('strkode_dealer', $strkode_dealer);
        $this->db->update('member', $data);
    }

    function get_dealer_by_id($kode) {
        $this->db->select('*');
        $this->db->where('strkode_dealer', $kode);
        return $this->db->get('member');
    }

    function get_all_pra() {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_pra = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function count_all_pra() {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_pra = 1');

        return $this->db->count_all_results('calon_manager');
    }

    function get_all_pra_filter($s) {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_pra = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        $this->db->like('member.strnama_dealer', $s['cari_nama']);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function get_all_cm() {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_cm = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function get_all_manager() {
		$cab = $this->session->userdata('id_cabang');
		if(!empty($cab)){
        $this->db->select('member.strnama_dealer,member.strnama_upline, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_manager = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
		$this->db->where('member.intid_cabang', $cab);
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
		}else{
		$this->db->select('member.strnama_dealer,member.strnama_upline, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_manager = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
			
			}
        return $this->db->get()->result();
    }

    function get_all_manager_filter($s) {
		$cab = $this->session->userdata('id_cabang');
		if(!empty($cab)){
			$this->db->select('member.strnama_dealer,member.strnama_upline, unit.strnama_unit, calon_manager.*');
			$this->db->from('calon_manager');
			$this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
			$this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
			$this->db->where('calon_manager.is_manager = 1');
			$this->db->where('calon_manager.is_breakdown = 0');
			$this->db->where('nota.intid_cabang', $cab);
			$this->db->where('MONTH(calon_manager.tanggal)', $s['bulan']);
			$this->db->order_by('calon_manager.tanggal', 'asc');
			
		}else{
			$this->db->select('member.strnama_dealer,member.strnama_upline, unit.strnama_unit, calon_manager.*');
			$this->db->from('calon_manager');
			$this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
			$this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
			$this->db->where('calon_manager.is_manager = 1');
			$this->db->where('calon_manager.is_breakdown = 0');
			$this->db->where('MONTH(calon_manager.tanggal)', $s['bulan']);
			$this->db->order_by('calon_manager.tanggal', 'asc');
		}
        return $this->db->get()->result();
    }

    function get_all_cm_filter($s) {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_cm = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        $this->db->like('member.strnama_dealer', $s['cari_nama']);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function get_all_register() {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_breakdown = 0');
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function get_all_register_filter($s) {
        $this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_breakdown = 0');
        $this->db->like('member.strnama_dealer', $s['cari_nama']);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
    }

    function delete($id) {
        $this->db->where('id', $id);
        $this->db->delete('calon_manager');
    }

    function delete_pra($id) {
        $this->db->where('strkode_dealer', $id);
        $this->db->delete('calon_manager');
    }

    function valid_dealer($kode) {
        $query = $this->db->get_where('calon_manager', array('strkode_dealer' => $kode));
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }
    }

    function valid_unit($kode) {
        $query = $this->db->get_where('unit', array('strnama_unit' => $kode));
        if ($query->num_rows() > 0) {
            return TRUE;
        }
        else {
            return FALSE;
        }
    }

    function viewDetailnota($nota) {
        $this->db->select('nota.*, member.strnama_dealer, nota_detail.intquantity, barang.strnama');
        $this->db->from('nota_detail');
        $this->db->join('nota', 'nota_detail.intid_nota = nota.intid_nota');
        $this->db->join('member','member.intid_dealer = nota.intid_dealer');
        $this->db->join('barang', 'barang.intid_barang = nota_detail.intid_barang');
        $this->db->where("nota.intno_nota", $nota);
        $this->db->where("nota.intid_jpenjualan", 1);
        //$this->db->group_by('nota.intid_nota');
        return $this->db->get()->result();
    }

    function view_anak1($kode) {
        $anak =array();
        $anak[] = $kode;
        //print_r($anak);
        $pro='';

        for ($c=0;$c<count($anak);$c++) {
            $pro.="'";
            $pro.=$anak[$c];
            $pro.="'";
            $pro.=", ";
        }

        $pro = substr($pro, 0, -2);
        $query = $this->db->query("SELECT member.strnama_dealer as name_child1,member.strkode_dealer as kode_child1 from member where strkode_upline = $pro");
        return $query->result();

    }

    function view_anak2($kode) {
        $query = $this->db->query("SELECT member.strnama_dealer,member.strkode_dealer  from member where strkode_upline ='$kode'");
        //var_dump("SELECT member.strnama_dealer,member.strkode_dealer  from member where strkode_upline ='$kode'");
        return $query->result();
    }

    function week_pv($strkode_upline, $week) {
        $this->db->select('sum(intpv) as pv');
        $this->db->from('nota');
        $this->db->join('member','nota.intid_dealer = member.intid_dealer');
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->where('month(nota.datetgl)', $week);
        $this->db->where('member.strkode_upline', $strkode_upline);
        return $this->db->get()->row();
    }
	
	function week_pv_pribadi($strkode_upline, $week) {
        $this->db->select('sum(intpv) as pv');
        $this->db->from('nota');
        $this->db->join('member','nota.intid_dealer = member.intid_dealer');
        $this->db->where('nota.intid_jpenjualan', 1);
        $this->db->where('month(nota.datetgl)', $week);
        $this->db->where('member.strkode_dealer', $strkode_upline);
        return $this->db->get()->row();
    }
	
	function weeks_pribadi_or($strkode, $week) {
        $this->db->select('sum(nota.inttotal_omset) as total');
        $this->db->from('nota');
        $this->db->where('nota.intid_dealer', $strkode);
        $this->db->where('nota.intid_week', $week);
        return $this->db->get()->result();
    }
	
	function penjualanrekrut_or($strkode, $week) {
        $query = $this->db->query("select sum(nota.inttotal_omset) total
		from nota, member
		where nota.intid_dealer = member.intid_dealer
		and member.strkode_upline = '$strkode'
		and member.intid_week = $week");
       return $query->result();
    }

	function penjualanpribadi_or001($strkode, $week) {
        $query = $this->db->query("select sum(nota.inttotal_omset) total
		from nota, member
		where nota.intid_dealer = member.intid_dealer
		and member.strkode_dealer = '$strkode'
		and nota.intid_week = $week");
       return $query->result();
    }
	/**
	* untuk mengubah update is_cm
	*/
	function update_member($kode, $data) {
        $this->db->where('strkode_dealer', $kode);
        $this->db->update('calon_manager', $data);
    }
	function get_date_registerCalonManager(){
		$tgl = date('Y-m-d');
		$temp = 0;
		$query = $this->db->query('select * from week where intbulan = month(curdate()) order by "dateweek_start" ASC');
		$row = $query->result();
		
		$query2 = $this->db->query('select * from week where curdate() between dateweek_start and dateweek_end order by "dateweek_start" ASC');
		$rok = $query2->result();
		//kalau intid_week masih masih dalam week pertama, 
		if($row[0]->intid_week == $rok[0]->intid_week){
			$temp = $rok[0]->intbulan;
		}else{
			//kalau bulannya 13 maka diset jadi bulan januari
			$temp = $rok[0]->intbulan + 1;
			if($temp == 13){
				$temp = 1;
			}
		}
		return $temp;
	}
	function data_pracm_bulan($bulan){
		$this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_breakdown = 0');
        $this->db->where('calon_manager.bulan = '.$bulan);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
	}
	function get_all_pra_bulan($bln) {
		$query = $this->db->query('select member.strnama_dealer, unit.strnama_unit, calon_manager.* 
			from calon_manager inner join member on member.strkode_dealer = calon_manager.strkode_dealer 
				inner join unit on unit.intid_unit = calon_manager.intid_unit 
			where calon_manager.is_pra = 1 and calon_manager.is_breakdown = 0 
			and calon_manager.bulan = "'.$bln.'"');
        /*
		$this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_pra = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        //$this->db->limit($limit, $offset);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        */
		return $query->result();
    }
	function get_all_pra_filter_lagi($s) {
        /*
		$this->db->select('member.strnama_dealer, unit.strnama_unit, calon_manager.*');
        $this->db->from('calon_manager');
        $this->db->join('member', 'member.strkode_dealer = calon_manager.strkode_dealer');
        $this->db->join('unit','unit.intid_unit = calon_manager.intid_unit');
        $this->db->where('calon_manager.is_pra = 1');
        $this->db->where('calon_manager.is_breakdown = 0');
        $this->db->like('member.strnama_dealer', $s['cari_nama']);
        $this->db->order_by('calon_manager.tanggal', 'asc');
        return $this->db->get()->result();
		*/
		$query = $this->db->query('select member.strnama_dealer, unit.strnama_unit, calon_manager.* 
		from calon_manager inner join member on member.strkode_dealer = calon_manager.strkode_dealer 
		inner join unit on unit.intid_unit = calon_manager.intid_unit 
		where 
			calon_manager.is_pra = 1 
			and calon_manager.is_breakdown = 0 
			and (member.strnama_dealer like "'.$s["cari_nama"].'%" or calon_manager.bulan = "'.$s["bulan"].'")
		order by "calon_manager.tanggal" ASC');
		return $query->result();
		
    }
	function rekursif_downline_update($strkode_dealer,$selisih,$leveldealer,$levelparent,$var,$id_unit){
		$var = "=".$var;
		$query = "";
		$query =('select member.intlevel_dealer, member.intparent_leveldealer, member.strkode_dealer from member where member.strkode_upline = "'.$strkode_dealer.'"');
		$result = $this->db->query($query);
		foreach($result->result() as $row){
			$this->rekursif_downline_update($row->strkode_dealer,$selisih,$leveldealer,$levelparent,$var,$id_unit);
			
			//echo($var.'d:'.$leveldealer.' - '.$row->intlevel_dealer.', P: '.$row->intparent_leveldealer.', kode_dealer : '.$row->strkode_dealer."<br />");
			
			$par = $row->intparent_leveldealer - $selisih;
			$d = $row->intlevel_dealer - $selisih;
			
			$level = array(
                        'intparent_leveldealer' => $par,
                        'intlevel_dealer' => $d,
                        'intid_unit' => $id_unit,
                );
			//echo($var.' ENDING : level_dealer :'.$d.', level_parent: '.$par.',  '.$row->strkode_dealer."<br />");
			
            $this->Cm_model->update($row->strkode_dealer, $level);
		}
	}
}
?>
